package com.skp.shaphan.savedConnections;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Vector;
import java.util.Map.Entry;

import com.skp.job.ProjectJobException;
import com.skp.shaphan.internalStorage.DBConnector;
import com.skp.shaphan.internalStorage.Fields;
import com.skp.shaphan.internalStorage.Tables;
import com.skp.shaphan.ui.connectioncontrols.ConnectionOptions;

/**
 * This class is responsible for loading and saving Connection
 * settings.
 * @author Stephen Phillips
 *
 */
public class SavedConnections {
	private static SavedConnections instance = null;
	private Vector<Integer> connectionIds;
	private Vector<ConnectionType> connectionTypes = null;
	
	private SavedConnections() {
		
	}
	
	public static SavedConnections getInstance() {
		if(instance == null) {
			instance = new SavedConnections();
		}
		return instance;
	}
	
	public Vector<SavedConnection> getConnections() {
		Vector<SavedConnection> connections = new Vector<SavedConnection>();
		connectionIds = new Vector<Integer>();

		Connection conn = DBConnector.getInstance().getConnection();
		try {
			PreparedStatement st = conn.prepareStatement("select * from " + Tables.DBConnections);
			ResultSet rs = st.executeQuery();
			while(rs.next()) {
				SavedConnection row = new SavedConnection();
				connectionIds.add(new Integer(rs.getInt(Fields.Connection_Id)));
				row.setId(rs.getInt(Fields.Connection_Id));
				row.setType(rs.getString(Fields.Connection_TypeName));
				row.setTypeClass(rs.getString(Fields.Connection_TypeClass));
				row.setName(rs.getString(Fields.Connection_Name));
				row.setIndex(connections.size());
				connections.add(row);
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			(new ProjectJobException(e)).displayException();
		}
		return connections;
	}
	
	public Vector<String> getColumnHeadings() {
		Vector<String> headings = new Vector<String>();
		headings.add("Connection Types");
		headings.add("Connection Name");
		return headings;
	}

	public void newConnection(String connectionName, ConnectionOptions connectionOptions, Properties properties) {
		Connection conn = DBConnector.getInstance().getConnection();
		int id = 0;
		try {
			PreparedStatement st = conn.prepareStatement("select max(" + Fields.Connection_Id + ") from " + Tables.DBConnections);
			ResultSet rs = st.executeQuery();
			if(rs.next()) {
				id = rs.getInt(1);
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			(new ProjectJobException(e)).displayException();
		}
		id++;
		try {
			PreparedStatement st = conn.prepareStatement("select * from " + Tables.DBConnections + " where 1 = 2", ResultSet.FETCH_FORWARD, ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = st.executeQuery();
			rs.moveToInsertRow();
			rs.updateInt(Fields.Connection_Id, id);
			rs.updateString(Fields.Connection_Name, connectionName);
			rs.updateString(Fields.Connection_TypeClass, connectionOptions.getClass().getName());
			rs.updateString(Fields.Connection_TypeName, connectionOptions.getUserFriendlyName());
			rs.insertRow();
			rs.close();
			st.close();
		} catch (SQLException e) {
			(new ProjectJobException(e)).displayException();
		}
		try {
			PreparedStatement st = conn.prepareStatement("select * from " + Tables.DBConnectionProperties + " where 1 = 2", ResultSet.FETCH_FORWARD, ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = st.executeQuery();
			int propertyId = 0;
			for(Entry<Object, Object> property : properties.entrySet()) {
				rs.moveToInsertRow();
				rs.updateInt(Fields.CProperty_ConnectionId, id);
				rs.updateInt(Fields.CProperty_PropertyId, ++propertyId);
				rs.updateString(Fields.CProperty_Name, property.getKey().toString());
				rs.updateString(Fields.CProperty_Value, property.getValue().toString());
				rs.insertRow();
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			(new ProjectJobException(e)).displayException();
		}
	}
	
	public int getIndexForID(int id) {
		if(connectionIds == null) {
			getConnections();
		}
		for(int x=0; x < connectionIds.size(); x++) {
			if(connectionIds.get(x).intValue() == id) {
				return x;
			}
		}
		return -1;
	}
	
	public int getIDforIndex(int index) {
		if(connectionIds == null) {
			getConnections();
		}
		return connectionIds.get(index).intValue();
	}

	public String getConnectionClass(int index) {
		Connection conn = DBConnector.getInstance().getConnection();
		if(connectionIds == null) {
			getConnections();
		}
		int id = connectionIds.get(index).intValue();
		String className = "";
		try {
			PreparedStatement st = conn.prepareStatement("select " + Fields.Connection_TypeClass + " from " + Tables.DBConnections + " where " + Fields.Connection_Id + " = ?");
			st.setInt(1, id);
			ResultSet rs = st.executeQuery();
			if(rs.next()) {
				className = rs.getString(Fields.Connection_TypeClass);
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			(new ProjectJobException(e)).displayException();
		}
		return className;
	}

	public Properties getConnectionProperties(int index) {
		Connection conn = DBConnector.getInstance().getConnection();
		int id = connectionIds.get(index).intValue();
		Properties props = new Properties();
		try {
			PreparedStatement st = conn.prepareStatement("select * from " + Tables.DBConnectionProperties + " where " + Fields.CProperty_ConnectionId + " = ?");
			st.setInt(1, id);
			ResultSet rs = st.executeQuery();
			while(rs.next()) {
				props.setProperty(rs.getString(Fields.CProperty_Name), rs.getString(Fields.CProperty_Value));
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			(new ProjectJobException(e)).displayException();
		}
		return props;
	}

	public String getConnectionName(int index) {
		Connection conn = DBConnector.getInstance().getConnection();
		int id = connectionIds.get(index).intValue();
		String className = "";
		try {
			PreparedStatement st = conn.prepareStatement("select " + Fields.Connection_Name + " from " + Tables.DBConnections + " where " + Fields.Connection_Id + " = ?");
			st.setInt(1, id);
			ResultSet rs = st.executeQuery();
			if(rs.next()) {
				className = rs.getString(Fields.Connection_Name);
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			(new ProjectJobException(e)).displayException();
		}
		return className;
	}

	public void updateConnection(SavedConnection savedConnection) {
		Connection conn = DBConnector.getInstance().getConnection();
		int id = savedConnection.getId();
		String className = "";
		try {
			PreparedStatement st = conn.prepareStatement(
					"update " + Tables.DBConnections + 
					" set " + Fields.Connection_TypeName + " = ?," + 
					Fields.Connection_TypeClass + " = ?," + 
					Fields.Connection_Name + " = ?" + 
					" where " + Fields.Connection_Id + " = ?"
			);
			
			st.setString(1, savedConnection.getType());
			st.setString(2, savedConnection.getTypeClass());
			st.setString(3, savedConnection.getName());
			st.setInt(4, id);
			st.executeUpdate();
			st.close();
		} catch (SQLException e) {
			(new ProjectJobException(e)).displayException();
		}
	}

	public void updateConnectionProperties(SavedConnection savedConnection, Properties properties) {
		Connection conn = DBConnector.getInstance().getConnection();
		try {
			PreparedStatement st = conn.prepareStatement(
					"select max(" + Fields.CProperty_PropertyId + ") " + 
					" from " + Tables.DBConnectionProperties + 
					" where " + Fields.CProperty_ConnectionId + " = ?"
			);
			st.setInt(1, savedConnection.getId());
			ResultSet rs = st.executeQuery();
			int propertyId = 0;
			if(rs.next()) {
				propertyId = rs.getInt(1);
			}
			rs.close();
			st.close();
			for(Entry<Object, Object> property : properties.entrySet()) {
				st = conn.prepareStatement(
						"select * from " + Tables.DBConnectionProperties + 
						" where " + Fields.CProperty_ConnectionId + " = ?" +
						" and " + Fields.CProperty_Name + " = ?", 
						ResultSet.FETCH_FORWARD, ResultSet.CONCUR_UPDATABLE
				);
				st.setInt(1, savedConnection.getId());
				st.setString(2, property.getKey().toString());
				rs = st.executeQuery();
				if(rs.next()) {
					System.out.println("Updating property " + property.getKey().toString() + " to " + property.getValue().toString());
					rs.updateString(Fields.CProperty_Value, property.getValue().toString());
					rs.updateRow();
				} else {
					System.out.println("Inserting new property #" + (propertyId + 1) + ": " + property.getKey().toString() + " = " + property.getValue().toString());
					rs.moveToInsertRow();
					rs.updateInt(Fields.CProperty_ConnectionId, savedConnection.getId());
					rs.updateInt(Fields.CProperty_PropertyId, ++propertyId);
					rs.updateString(Fields.CProperty_Name, property.getKey().toString());
					rs.updateString(Fields.CProperty_Value, property.getValue().toString());
					rs.insertRow();
				}
				rs.close();
				st.close();
			}
		} catch (SQLException e) {
			(new ProjectJobException(e)).displayException();
		}
	}
	
	public Vector<ConnectionType> getConnectionTypes() {
		if(connectionTypes == null) {
			loadConnectionTypes();
		}
		return connectionTypes;
	}
	
	private void loadConnectionTypes() {
		connectionTypes = new Vector<ConnectionType>();
		connectionTypes.add(new ConnectionType("Derby"));
		connectionTypes.add(new ConnectionType("SQL Server"));
		connectionTypes.add(new ConnectionType("ODBC"));
		connectionTypes.add(new ConnectionType("Oracle"));
		connectionTypes.add(new ConnectionType("MySQL"));
		connectionTypes.add(new ConnectionType("jTDS"));
	}
}
